1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmBusFeeReceipt_Staff
4 Sub fillPaymentID()
5 Try
6 Dim CN As New SqlConnection(cs)
7 CN.Open()
8 adp = New SqlDataAdapter()
9 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(PaymentID) FROM BusFeePayment_Staff", CN)
10 ds = New DataSet("ds")
11 adp.Fill(ds)
12 dtable = ds.Tables(0)
13 cmbPaymentID.Items.Clear()
14 For Each drow As DataRow In dtable.Rows
15 cmbPaymentID.Items.Add(drow(0).ToString())
16 Next
17
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22
23 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
24 fillPaymentID()
25 End Sub
26 Sub Reset()
27 cmbPaymentID.Text = ""
28 fillPaymentID()
29 End Sub
30 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
31 Reset()
32 End Sub
33
34
35 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
36 Me.Close()
37 End Sub
38
39 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
40 Try
41 If cmbPaymentID.Text = "" Then
42 MessageBox.Show("Please select payment id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
43 cmbPaymentID.Focus()
44 Exit Sub
45 End If
46 Cursor = Cursors.WaitCursor
47 Timer1.Enabled = True
48 Dim rpt As New rptBusFeeReceipt_Staff 'The report you created.
49 Dim myConnection As SqlConnection
50 Dim MyCommand As New SqlCommand()
51 Dim myDA As New SqlDataAdapter()
52 Dim myDS As New DataSet 'The DataSet you created.
53 myConnection = New SqlConnection(cs)
54 MyCommand.Connection = myConnection
55 MyCommand.CommandText = "SELECT BusFeePayment_Staff.Id, BusFeePayment_Staff.BFP_ID, BusFeePayment_Staff.PaymentID, BusFeePayment_Staff.BusHolderID, BusFeePayment_Staff.Session, BusFeePayment_Staff.Installment,BusFeePayment_Staff.TotalFee, BusFeePayment_Staff.DiscountPer, BusFeePayment_Staff.DiscountAmt, BusFeePayment_Staff.PreviousDue, BusFeePayment_Staff.Fine, BusFeePayment_Staff.GrandTotal, BusFeePayment_Staff.TotalPaid, BusFeePayment_Staff.ModeOfPayment, BusFeePayment_Staff.PaymentModeDetails, BusFeePayment_Staff.PaymentDate, BusFeePayment_Staff.PaymentDue,BusCardHolder_Staff.BCH_ID, BusCardHolder_Staff.StaffID, BusCardHolder_Staff.BusNo, BusCardHolder_Staff.Location, BusCardHolder_Staff.JoiningDate, BusCardHolder_Staff.Status, Staff.St_ID,Staff.StaffID AS Expr1, Staff.StaffName, Staff.DateOfJoining, Staff.Gender, Staff.FatherName, Staff.TemporaryAddress, Staff.PermanentAddress, Staff.Designation, Staff.Qualifications, Staff.DOB, Staff.PhoneNo,Staff.MobileNo, Staff.Photo, Staff.ClassType, Staff.SchoolID, Staff.AccountName, Staff.AccountNumber, Staff.Bank, Staff.Branch, Staff.IFSCcode, Staff.Salary, SchoolInfo.S_Id,SchoolInfo.SchoolName, SchoolInfo.Address, SchoolInfo.ContactNo, SchoolInfo.AltContactNo, SchoolInfo.FaxNo, SchoolInfo.Email , SchoolInfo.Website, SchoolInfo.Logo, SchoolInfo.RegistrationNo,SchoolInfo.DiseNo, SchoolInfo.IndexNo, SchoolInfo.EstablishedYear, SchoolInfo.Class, SchoolInfo.SchoolType FROM BusFeePayment_Staff INNER JOIN BusCardHolder_Staff ON BusFeePayment_Staff.BusHolderID = BusCardHolder_Staff.BCH_ID INNER JOIN Staff ON BusCardHolder_Staff.StaffID = Staff.St_ID INNER JOIN SchoolInfo ON Staff.SchoolID = SchoolInfo.S_Id where BusFeePayment_Staff.PaymentID='" & cmbPaymentID.Text & "'"
56 MyCommand.CommandType = CommandType.Text
57 myDA.SelectCommand = MyCommand
58 myDA.Fill(myDS, "SchoolInfo")
59 myDA.Fill(myDS, "Staff")
60 myDA.Fill(myDS, "BusCardHolder_Staff")
61 myDA.Fill(myDS, "BusFeePayment_Staff")
62 rpt.SetDataSource(myDS)
63 rpt.SetParameterValue("p1", txtStaffID.Text)
64 frmReport.CrystalReportViewer1.ReportSource = rpt
65 frmReport.ShowDialog()
66 Catch ex As Exception
67 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
68 End Try
69 End Sub
70
71 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
72 Cursor = Cursors.Default
73 Timer1.Enabled = False
74 End Sub
75
76 Private Sub cmbPaymentID_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbPaymentID.SelectedIndexChanged
77 Try
78 con = New SqlConnection(cs)
79 con.Open()
80 cmd = con.CreateCommand()
81 cmd.CommandText = "SELECT Staff.StaffID FROM Staff,BusCardHolder_Staff,BusFeePayment_Staff where Staff.St_ID=BusCardHolder_Staff.StaffID and BusCardHolder_Staff.BCH_ID=BusFeePayment_Staff.BusHolderID and PaymentID='" & cmbPaymentID.Text & "'"
82 cmd.Parameters.AddWithValue("@d1", txtStaffID.Text)
83 rdr = cmd.ExecuteReader()
84 If rdr.Read() Then
85 txtStaffID.Text = rdr.GetValue(0).ToString().Trim()
86 End If
87 If (rdr IsNot Nothing) Then
88 rdr.Close()
89 End If
90 If con.State = ConnectionState.Open Then
91 con.Close()
92 End If
93 Catch ex As Exception
94 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
95 End Try
96 End Sub
97 End Class
